﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for D_Stock_Adjustment
/// </summary>
/// 
namespace Bestlife.ERP.DataAccess
{
    public class D_Stock_Adjustment
    {
        public static int lastid;
        public static decimal QuantityDB;
        public D_Stock_Adjustment()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public static Boolean adjuststock(Bestlife.ERP.Entities.E_Stock_Adjustment stock, string user)
        {
            Boolean upd2 = false;
            try
            {

                DateTime date = System.DateTime.Now;
                
                        String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                        Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                        string updtxt2 = "INSERT INTO [tblStock_Adjustment]([Branch_Id],[Status],[User_Id],[Date_Create],[Total]) VALUES ('" + stock.Branch_id + "','" + stock.Status + "','" + user + "','" + date + "','" + stock.Tot + "')";
                        Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(updtxt2);
                        if (edit1)
                        {
                            upd2 = true;
                        }


            }
            catch
            {
            }
            return upd2;
        }

        public static Boolean stockchange(string proid, decimal qty, string user, string code)
        {
            Boolean upd2 = false;
            try
            {

                        DateTime date = System.DateTime.Now;
               
                        string product = getproname(proid);
                        String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                        Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                        string updtxt2 = "UPDATE [Tbl_Issue_Stock] SET[Qty_in_stock] = '"+qty+"'WHERE [Code]='"+code+"' and [Pro_ID]='"+proid+"'";
                      
                        Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(updtxt2);
                        if (edit1)
                        {
                            upd2 = true;
                        }
                   
            }
            catch
            {
            }
            return upd2;
        }

        public static Decimal ReturnStockQuantitypanadura(Bestlife.ERP.Entities.E_Stock_Adjustment stock,string user)
        {
            SqlDataReader rdR;


            try
            {
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                string product = getproname(stock.Proid);
                String Ssql = "SELECT Qty_in_stock from Tbl_Issue_Stock  where [Pro_ID]='" + stock.Proid + "' and [Code]='" + stock.Code + "' ";
                //SqlDataReader ldr = Bestlife.ERP.DataAccess.clsCon.ReadTable(Ssql);


                rdR = Connection.ReadTable(Ssql);


                if (rdR.Read())
                {
                    QuantityDB = Decimal.Parse(rdR["Qty_in_stock"].ToString());

                }
                rdR.Close();
            }
            catch (Exception)
            {

            }

            return QuantityDB;

        }

        public static string getstatus(string said)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Status]  FROM [tblStock_Adjustment]  where [SA_id]='" + said + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    string proname = rdR["Status"].ToString();
                    rdR.Close();
                    return proname;
                }
                rdR.Close();
            }
            catch
            {
            }

            string error = "error";
            return error;

        }

        public static string getbranch(string said)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Branch_Id]  FROM [tblStock_Adjustment]  where [SA_id]='" + said + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    string proname = rdR["Branch_Id"].ToString();
                    rdR.Close();
                    return proname;
                }
            rdR.Close();
            }
            catch
            {
            }

            string error = "error";
            return error;

        }

        public static string getcode(string stockid)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT[Code] FROM [Tbl_Stock] where Sto_ID='" + stockid + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    string proname = rdR["Code"].ToString();
                    rdR.Close();
                    return proname;
                }
                rdR.Close();
            }
            catch
            {
            }

            string error = "error";
            return error;

        }

        public static Boolean adjuststockdetails(Bestlife.ERP.Entities.E_Stock_Adjustment stock,string user)
        {
            Boolean upd2 = false;
            try
            {

                DateTime date = System.DateTime.Now;
                //Decimal QuantityinDB = Bestlife.ERP.DataAccess.D_Stock_Adjustment.ReturnStockQuantitykandy(stock.Proid,stock.Code);

                //if (stock.Qty > 0)
                //{
                //    Decimal QuantityToDB = QuantityinDB + Decimal.Parse(stock.Qty.ToString());
                //    if (QuantityToDB >= 0)
                //    {

                string code = getcode(stock.Stoid);
                        String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                        Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                        string updtxt2 = "INSERT INTO [tblStock_Adjustment_Details]([SA_ID],[Sto_id],[Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES ('"+stock.SAid+"','" + stock.Stoid + "','" + stock.Qty + "','" + user + "','" + date + "','" + stock.Proid + "','" + code + "')";
                        Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(updtxt2);
                        if (edit1)
                        {
                            upd2 = true;
                        }
                //    }
                //    else
                //    {
                //        //print we have only QuantityinDB now !!!
                //    }
                //}
                //else
                //{
                //    // sory we have nt any Product 
                //}
            }
            catch
            {
            }
            return upd2;
        }

        public static string getproname(string pro)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Pro_Name] FROM [Tbl_Product] where [Pro_ID]='" + pro + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    string proname = rdR["Pro_Name"].ToString();
                    rdR.Close();
                    return proname;
                }
                rdR.Close();
            }
            catch
            {
            }

            string error = "error";
            return error;

        }

        public static Decimal ReturnStockQuantitykandy(string pro_id, string code)
        {
            SqlDataReader rdR;


            try
            {
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                string product = getproname(pro_id);
                String Ssql = "SELECT [Kandy_Quantity] from Tbl_Issue_Stock  where [Pro_ID]='" + pro_id + "' and [Code]='" + code + "' ";
                //SqlDataReader ldr = Bestlife.ERP.DataAccess.clsCon.ReadTable(Ssql);


                rdR = Connection.ReadTable(Ssql);


                if (rdR.Read())
                {
                    QuantityDB = Decimal.Parse(rdR["Kandy_Quantity"].ToString());

                }
                rdR.Close();
            }
            catch (Exception)
            {

            }

            return QuantityDB;

        }
      
        public static Boolean stockadjustmentaddPanadura(Bestlife.ERP.Entities.E_Stock_Adjustment stock, string user)
        {
            Boolean upd2 = false;
            try
            {

                DateTime date = System.DateTime.Now;
                Decimal QuantityinDB = ReturnStockQuantitypanadura(stock, user);

                if (stock.Qty > 0)
                {
                    Decimal QuantityToDB = QuantityinDB + Decimal.Parse(stock.Qty.ToString());
                    if (QuantityToDB >= 0)
                    {

                        string product = getproname(stock.Proid);
                        String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                        Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                        string updtxt2 = "INSERT INTO [Tbl_Issue_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + product + "','" + stock.Qty + "','" + user + "','" + date + "','" + stock.Proid + "','" + stock.Code + "')";
                        //String updtxt2 = "INSERT INTO [Tbl_Stock_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + product + "','" + qty + "','" + user + "','" + date + "','" + proname + "','" + code + "')";

                        //String updtxt2 = "UPDATE Bonus  SET  T_commision='999' WHERE User_id='001'";
                        Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(updtxt2);
                        if (edit1)
                        {
                            upd2 = true;
                        }
                    }
                    else
                    {
                        //print we have only QuantityinDB now !!!
                    }
                }
                else
                {
                    // sory we have nt any Product 
                }
            }
            catch
            {
            }
            return upd2;

        }
        public static Boolean stockadjustmentaddKandy(Bestlife.ERP.Entities.E_Stock_Adjustment stock, string user)
        {
            SqlDataReader rdR;
            Boolean upd2 = false;
         
            try
            {
               
                DateTime date = System.DateTime.Now;
                Decimal QuantityinDB = ReturnStockQuantitykandy(stock.Proid,stock.Code);

                if (stock.Qty > 0)
                {
                    Decimal QuantityToDB = QuantityinDB + Decimal.Parse(stock.Qty.ToString());
                    if (QuantityToDB >= 0)
                    {

                        string product = getproname(stock.Proid);
                        String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                        Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                        string updtxt2 = "INSERT INTO [Tbl_Kandy_Stock_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + product + "','" + stock.Qty + "','" + user + "','" + date + "','" + stock.Proid + "','" + stock.Code + "')";
                        //String updtxt2 = "INSERT INTO [Tbl_Stock_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + product + "','" + qty + "','" + user + "','" + date + "','" + proname + "','" + code + "')";

                        //String updtxt2 = "UPDATE Bonus  SET  T_commision='999' WHERE User_id='001'";
                        Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(updtxt2);
                        if (edit1)
                        {
                            upd2 = true;
                        }
                    }
                    else
                    {
                        //print we have only QuantityinDB now !!!
                    }
                }
                else
                {
                    // sory we have nt any Product 
                }
            }
            catch
            {
            }
            return upd2;


        }
        public static Boolean stockadjustmentminusKandy(Bestlife.ERP.Entities.E_Stock_Adjustment stock, string user)
        {
            Boolean upd2 = false;
            try
            {

                DateTime date = System.DateTime.Now;
                Decimal QuantityinDB = ReturnStockQuantitykandy(stock.Proid, stock.Code);

                if (QuantityinDB > 0)
                {
                    Decimal QuantityToDB = QuantityinDB - Decimal.Parse(stock.Qty.ToString());
                    if (QuantityToDB >= 0)
                    {

                        string product = getproname(stock.Proid);
                        String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                        Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                        string updtxt2 = "UPDATE [Tbl_Issue_Stock]   SET [Kandy_Quantity] = '" + QuantityToDB + "'WHERE [Pro_ID]='" + stock.Proid + "' and [Code]='" + stock.Code + "'";
                        //String updtxt2 = "INSERT INTO [Tbl_Stock_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + product + "','" + qty + "','" + user + "','" + date + "','" + proname + "','" + code + "')";

                        //String updtxt2 = "UPDATE Bonus  SET  T_commision='999' WHERE User_id='001'";
                        Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(updtxt2);
                        if (edit1)
                        {
                            upd2 = true;
                        }
                    }
                    else
                    {
                        //print we have only QuantityinDB now !!!
                    }
                }
                else
                {
                    // sory we have nt any Product 
                }
            }
            catch
            {
            }
            return upd2;
        }
        public static Boolean stockadjustmentminusPanadura(Bestlife.ERP.Entities.E_Stock_Adjustment stock, string user)
        {
            Boolean upd2 = false;
            try
            {

                DateTime date = System.DateTime.Now;
                Decimal QuantityinDB = ReturnStockQuantitypanadura(stock, user);

                if (QuantityinDB > 0)
                {
                    Decimal QuantityToDB = QuantityinDB - Decimal.Parse(stock.Qty.ToString());
                    if (QuantityToDB >= 0)
                    {

                        string product = getproname(stock.Proid);
                        String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                        Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                        string updtxt2 = "UPDATE [Tbl_Issue_Stock]   SET [Qty_in_stock] = '" + QuantityToDB + "'WHERE [Pro_ID]='" + stock.Proid + "' and [Code]='" + stock.Code + "'";
                        //String updtxt2 = "INSERT INTO [Tbl_Stock_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + product + "','" + qty + "','" + user + "','" + date + "','" + proname + "','" + code + "')";

                        //String updtxt2 = "UPDATE Bonus  SET  T_commision='999' WHERE User_id='001'";
                        Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(updtxt2);
                        if (edit1)
                        {
                            upd2 = true;
                        }
                    }
                    else
                    {
                        //print we have only QuantityinDB now !!!
                    }
                }
                else
                {
                    // sory we have nt any Product 
                }
            }
            catch
            {
            }
            return upd2;

        }
        public static int lastGRNid()
        {
            SqlDataReader rdR;
            try
            {

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT MAX(SA_ID) as lastid  FROM [tblStock_Adjustment]";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    lastid = (int)rdR["lastid"] + 1;

                }
                rdR.Close();
            }
            catch
            {
            }
            return lastid;

        }
        ////public static List<Bestlife.ERP.Entities.E_Stock_Adjustment> getAllproduct(string said)
        //{
        //    List<Bestlife.ERP.Entities.E_Stock_Adjustment> stock = new List<Entities.E_Stock_Adjustment>();
        //    SqlDataReader rdR;
        //    SqlDataReader rdR1;
        //    try
        //    {
        //        int cc;
        //        DateTime date = System.DateTime.Now;
        //        String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
        //        Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
        //         String Ssql1="SELECT COUNT (Pro_ID) as cc  FROM [tblStock_Adjustment_Details] where SA_ID='"+said+"'";
        //         rdR1 = Connection.ReadTable(Ssql1);
        //         if (rdR1.Read())
        //         {
        //            cc =(int)rdR["cc"];
        //         }
        //        String Ssql = "SELECT [SA_ID],[Sto_ID],[Qty],[User_Create],[Date_Create],[Last_Modify],[Pro_ID],[Code]FROM [tblStock_Adjustment_Details] where SA_ID='" + said+ "'";

        //        rdR = Connection.ReadTable(Ssql);

        //        if (rdR.Read())
        //        {
        //            while(cc)
        //            {
        //            Bestlife.ERP.Entities.E_Stock_Adjustment vv = new Entities.E_Stock_Adjustment();
        //            vv.Code = rdR["Code"].ToString();
        //            vv.Proid = rdR["Pro_ID"].ToString();
        //            vv.Qty =Convert.ToDecimal(rdR["Qty"].ToString());
        //            vv.SAid = rdR["SA_ID"].ToString();
        //            vv.Stoid = rdR["Sto_ID"].ToString();
        //            stock.Add(vv);
        //            rdR.Close();
        //            }
                    
        //        }
        //        rdR.Close();
        //             rdR1.Close();
        //    }
        //    catch
        //    {
        //    }

        //    return stock;

        //}
    }
}